package com.application.chiettinhdienluc.db;

import java.util.ArrayList;

import android.content.ContentValues;
import android.database.Cursor;
import android.text.TextUtils;

import com.application.chiettinhdienluc.model.ChietTinhModel;

public class ChietTinhDataSource {
	// Database fields
	private final MySQLiteHelper dbHelper;

	// table name
	public static final String TABLE_HSO_CHIETTINH = "HSO_CHIETTINH";

	// table column names
	public static final String COLUMN_BANCTINH_ID = "BANCTINH_ID";
	public static final String COLUMN_CPHI_TT = "CPHI_TT";
	public static final String COLUMN_CPHI_VT = "CPHI_VT";
	public static final String COLUMN_CPHI_VL = "CPHI_VL";
	public static final String COLUMN_CPHI_VC = "CPHI_VC";
	public static final String COLUMN_CPHI_NC = "CPHI_NC";
	public static final String COLUMN_CPHI_MM = "CPHI_MM";
	public static final String COLUMN_CPHI_TTK = "CPHI_TTK";
	public static final String COLUMN_CPHI_C = "CPHI_C";
	public static final String COLUMN_CPHI_TL = "CPHI_TL";
	public static final String COLUMN_CPHI_K = "CPHI_K";
	public static final String COLUMN_TIEN_GTT = "TIEN_GTT";
	public static final String COLUMN_TIEN_GTGT = "TIEN_GTGT";
	public static final String COLUMN_SO_TIEN = "SO_TIEN";
	public static final String COLUMN_LAM_TRON = "LAM_TRON";
	public static final String COLUMN_LAN = "LAN";
	public static final String COLUMN_TRANG_THAI = "TRANG_THAI";
	public static final String COLUMN_THUC_HIEN = "THUC_HIEN";
	public static final String COLUMN_MA_DVIQLY = "MA_DVIQLY";
	public static final String COLUMN_MA_YCAU_KNAI = "MA_YCAU_KNAI";
	public static final String COLUMN_MALOAI_HS = "MALOAI_HS";
	public static final String COLUMN_TEN_KHANG = "TEN_KHANG";
	public static final String COLUMN_DTHOAI_DVU = "DTHOAI_DVU";
	public static final String COLUMN_BB_KSAT = "BB_KSAT";
	public static final String COLUMN_NGAY_KSAT = "NGAY_KSAT";
	public static final String COLUMN_NVIEN_KS = "NVIEN_KS";
	public static final String COLUMN_TTNHA = "TTNHA";
	public static final String COLUMN_TTHD = "TTHD";
	public static final String COLUMN_MA_TRAM = "MA_TRAM";
	public static final String COLUMN_TEN_TRAM = "TEN_TRAM";
	public static final String COLUMN_CSUAT_TRAM = "CSUAT_TRAM";
	public static final String COLUMN_MA_GCST = "MA_GCST";
	public static final String COLUMN_MA_GCSP = "MA_GCSP";
	public static final String COLUMN_NGAY_CTINH = "NGAY_CTINH";
	public static final String COLUMN_DVI_DTU = "DVI_DTU";
	public static final String COLUMN_NGAY_CHINHSUA = "NGAY_CHINHSUA";
	public static final String COLUMN_NGAY_SUBMIT = "NGAY_SUBMIT";
	public static final String COLUMN_TRANG_THAI_HSO = "TRANG_THAI_HSO";
	public static final String COLUMN_DIA_CHI = "DIA_CHI";
	public static final String COLUMN_NGAY_ND = "NGAY_ND";

	private final static String TAG = ChietTinhDataSource.class.getSimpleName();

	public ChietTinhDataSource() {
		dbHelper = MySQLiteHelper.getInstance();
	}

	public boolean checkHoSoExist(String tenKH, String diachi, String maYC) {

		boolean existed = false;
		Cursor c = null;
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{	
				c = dbHelper.getDb().rawQuery("select rowid from "
						+ ChietTinhModel.CHIETTINH_TABLE_NAME + " where TEN_KHANG = " 
						+ "'" + tenKH  + "' and "  
						+ " TTNHA = "  + "'" + diachi  + "' and"
						+ " MA_YCAU_KNAI  = " + "'" + maYC + "'" , null);		

				if (c != null && c.getCount() > 0){
					existed = true;			
				}
			}
		} catch (Exception e) {
		}
		finally{
			if(c != null) 
			{
				c.close();
				c = null;
			}
		}

		return existed;	
	}


	public long insert(ChietTinhModel chiettinh) {
		long row_id = -1;

		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				ContentValues cv = new ContentValues();

				cv.put(COLUMN_BANCTINH_ID, chiettinh.get_MaBanChietTinh());
				cv.put(COLUMN_CPHI_TT, chiettinh.get_ChiPhiTT());
				cv.put(COLUMN_CPHI_VT, chiettinh.get_ChiPhiVT());
				cv.put(COLUMN_CPHI_VL, chiettinh.get_ChiPhiVL());
				cv.put(COLUMN_CPHI_VC, chiettinh.get_ChiPhiVC());
				cv.put(COLUMN_CPHI_NC, chiettinh.get_ChiPhiNC());
				cv.put(COLUMN_CPHI_MM, chiettinh.get_ChiPhiMM());
				cv.put(COLUMN_CPHI_TTK, chiettinh.get_ChiPhiTTK());
				cv.put(COLUMN_CPHI_C, chiettinh.get_ChiPhiC());
				cv.put(COLUMN_CPHI_TL, chiettinh.get_ChiPhiTL());
				cv.put(COLUMN_CPHI_K, chiettinh.get_ChiPhiK());
				cv.put(COLUMN_TIEN_GTT, chiettinh.get_TienGTT());
				cv.put(COLUMN_TIEN_GTGT, chiettinh.get_TienGTGT());
				cv.put(COLUMN_SO_TIEN, chiettinh.get_SoTien());
				cv.put(COLUMN_LAM_TRON, chiettinh.get_LamTron());
				cv.put(COLUMN_LAN, chiettinh.get_Lan());
				cv.put(COLUMN_TRANG_THAI, !TextUtils.isEmpty(chiettinh.get_TrangThai()) ? chiettinh.get_TrangThai() : "");
				cv.put(COLUMN_THUC_HIEN, !TextUtils.isEmpty(chiettinh.get_ThucHien()) ? chiettinh.get_ThucHien() : "");
				cv.put(COLUMN_MA_DVIQLY, !TextUtils.isEmpty(chiettinh.get_MaDonViQuanLy()) ? chiettinh.get_MaDonViQuanLy() : "");
				cv.put(COLUMN_MA_YCAU_KNAI, !TextUtils.isEmpty(chiettinh.get_MaYeuCauKhieuNai()) ? chiettinh.get_MaYeuCauKhieuNai() : "");
				cv.put(COLUMN_MALOAI_HS, !TextUtils.isEmpty(chiettinh.get_MaLoaiHoSo()) ? chiettinh.get_MaLoaiHoSo() : "");
				cv.put(COLUMN_TEN_KHANG, !TextUtils.isEmpty(chiettinh.get_TenKhachHang()) ? chiettinh.get_TenKhachHang() : "");
				cv.put(COLUMN_DTHOAI_DVU, !TextUtils.isEmpty(chiettinh.get_DienThoaiDichVu()) ? chiettinh.get_DienThoaiDichVu() : "");
				cv.put(COLUMN_BB_KSAT, !TextUtils.isEmpty(chiettinh.get_BienBanKhaoSat()) ? chiettinh.get_BienBanKhaoSat() : "");
				cv.put(COLUMN_NGAY_KSAT, !TextUtils.isEmpty(chiettinh.get_NgayKhaoSat().toString()) ? chiettinh.get_NgayKhaoSat().toString() : "");
				cv.put(COLUMN_NVIEN_KS, !TextUtils.isEmpty(chiettinh.get_NhanVienKhaoSat()) ? chiettinh.get_NhanVienKhaoSat() : "");
				cv.put(COLUMN_TTNHA, !TextUtils.isEmpty(chiettinh.get_ThongTinNha()) ? chiettinh.get_ThongTinNha() : "");
				cv.put(COLUMN_TTHD, !TextUtils.isEmpty(chiettinh.get_ThongTinHopDong()) ? chiettinh.get_ThongTinHopDong() : "");
				cv.put(COLUMN_MA_TRAM, !TextUtils.isEmpty(chiettinh.get_MaTram()) ? chiettinh.get_MaTram() : "");
				cv.put(COLUMN_TEN_TRAM, !TextUtils.isEmpty(chiettinh.get_TenTram()) ? chiettinh.get_TenTram() : "");
				cv.put(COLUMN_CSUAT_TRAM, !TextUtils.isEmpty(chiettinh.get_CongSuatTram()) ? chiettinh.get_CongSuatTram() : "");
				cv.put(COLUMN_MA_GCST, !TextUtils.isEmpty(chiettinh.get_MaGCST()) ? chiettinh.get_MaGCST() : "");
				cv.put(COLUMN_MA_GCSP, !TextUtils.isEmpty(chiettinh.get_MaGCSP()) ? chiettinh.get_MaGCSP() : "");
				cv.put(COLUMN_NGAY_CTINH, !TextUtils.isEmpty(chiettinh.get_NgayChietTinh()) ? chiettinh.get_NgayChietTinh() : "");
				cv.put(COLUMN_DVI_DTU, !TextUtils.isEmpty(chiettinh.get_DonViDauTu()) ? chiettinh.get_DonViDauTu() : "");
				cv.put(COLUMN_NGAY_CHINHSUA, !TextUtils.isEmpty(chiettinh.get_NgayChinhSua()) ? chiettinh.get_NgayChinhSua() : "" );
				cv.put(COLUMN_NGAY_SUBMIT, !TextUtils.isEmpty(chiettinh.get_NgaySubmit()) ? chiettinh.get_NgaySubmit() : "");
				cv.put(COLUMN_TRANG_THAI_HSO, !TextUtils.isEmpty(chiettinh.get_TrangThaiHoSo()) ? chiettinh.get_TrangThaiHoSo() : "1");
				cv.put(COLUMN_DIA_CHI, !TextUtils.isEmpty(chiettinh.get_DiaChi()) ? chiettinh.get_DiaChi() : "");
				cv.put(COLUMN_NGAY_ND, !TextUtils.isEmpty(chiettinh.get_NgayND().toString()) ? chiettinh.get_NgayND().toString() : "");

				row_id = dbHelper.getDb().insert(TABLE_HSO_CHIETTINH, null, cv);
				chiettinh.set_Id(row_id);
			}
		} catch (Exception e) {
		}
		finally{
		}

		return row_id;
	}

	public int updateTrangThaiHoSo(Long id, String maBanCTinh) {
		int numRowEffect = 0;

		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				ContentValues cv = new ContentValues();

				cv.put(COLUMN_BANCTINH_ID, maBanCTinh);	
				cv.put(COLUMN_TRANG_THAI_HSO, "3");	

				String whereClause = "rowid" + "=?";
				String[] whereArgs = new String[] {  id + "" };

				numRowEffect = dbHelper.getDb().update(TABLE_HSO_CHIETTINH, cv,	whereClause, whereArgs);
			}
		} catch (Exception e) {
		}
		finally{
		}

		return numRowEffect;
	}

	public int update(ChietTinhModel chiettinh) {
		int numRowEffect = 0;
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{

				ContentValues cv = new ContentValues();

//				cv.put(COLUMN_BANCTINH_ID, chiettinh.get_MaBanChietTinh());
				cv.put(COLUMN_CPHI_TT, chiettinh.get_ChiPhiTT());
				cv.put(COLUMN_CPHI_VT, chiettinh.get_ChiPhiVT());
				cv.put(COLUMN_CPHI_VL, chiettinh.get_ChiPhiVL());
				cv.put(COLUMN_CPHI_VC, chiettinh.get_ChiPhiVC());
				cv.put(COLUMN_CPHI_NC, chiettinh.get_ChiPhiNC());
				cv.put(COLUMN_CPHI_MM, chiettinh.get_ChiPhiMM());
				cv.put(COLUMN_CPHI_TTK, chiettinh.get_ChiPhiTTK());
				cv.put(COLUMN_CPHI_C, chiettinh.get_ChiPhiC());
				cv.put(COLUMN_CPHI_TL, chiettinh.get_ChiPhiTL());
				cv.put(COLUMN_CPHI_K, chiettinh.get_ChiPhiK());
				cv.put(COLUMN_TIEN_GTT, chiettinh.get_TienGTT());
				cv.put(COLUMN_TIEN_GTGT, chiettinh.get_TienGTGT());
				cv.put(COLUMN_SO_TIEN, chiettinh.get_SoTien());
				cv.put(COLUMN_LAM_TRON, chiettinh.get_LamTron());
				cv.put(COLUMN_LAN, chiettinh.get_Lan());
				cv.put(COLUMN_TRANG_THAI, !TextUtils.isEmpty(chiettinh.get_TrangThai()) ? chiettinh.get_TrangThai() : "");
				cv.put(COLUMN_THUC_HIEN, !TextUtils.isEmpty(chiettinh.get_ThucHien()) ? chiettinh.get_ThucHien() : "");
				cv.put(COLUMN_MA_DVIQLY, !TextUtils.isEmpty(chiettinh.get_MaDonViQuanLy()) ? chiettinh.get_MaDonViQuanLy() : "");
				cv.put(COLUMN_MA_YCAU_KNAI, !TextUtils.isEmpty(chiettinh.get_MaYeuCauKhieuNai()) ? chiettinh.get_MaYeuCauKhieuNai() : "");
				cv.put(COLUMN_MALOAI_HS, !TextUtils.isEmpty(chiettinh.get_MaLoaiHoSo()) ? chiettinh.get_MaLoaiHoSo() : "");
				cv.put(COLUMN_TEN_KHANG, !TextUtils.isEmpty(chiettinh.get_TenKhachHang()) ? chiettinh.get_TenKhachHang() : "");
				cv.put(COLUMN_DTHOAI_DVU, !TextUtils.isEmpty(chiettinh.get_DienThoaiDichVu()) ? chiettinh.get_DienThoaiDichVu() : "");
				cv.put(COLUMN_BB_KSAT, !TextUtils.isEmpty(chiettinh.get_BienBanKhaoSat()) ? chiettinh.get_BienBanKhaoSat() : "");
				cv.put(COLUMN_NGAY_KSAT, !TextUtils.isEmpty(chiettinh.get_NgayKhaoSat().toString()) ? chiettinh.get_NgayKhaoSat().toString() : "");
				cv.put(COLUMN_NVIEN_KS, !TextUtils.isEmpty(chiettinh.get_NhanVienKhaoSat()) ? chiettinh.get_NhanVienKhaoSat() : "");
				cv.put(COLUMN_TTNHA, !TextUtils.isEmpty(chiettinh.get_ThongTinNha()) ? chiettinh.get_ThongTinNha() : "");
				cv.put(COLUMN_TTHD, !TextUtils.isEmpty(chiettinh.get_ThongTinHopDong()) ? chiettinh.get_ThongTinHopDong() : "");
				cv.put(COLUMN_MA_TRAM, !TextUtils.isEmpty(chiettinh.get_MaTram()) ? chiettinh.get_MaTram() : "");
				cv.put(COLUMN_TEN_TRAM, !TextUtils.isEmpty(chiettinh.get_TenTram()) ? chiettinh.get_TenTram() : "");
				cv.put(COLUMN_CSUAT_TRAM, !TextUtils.isEmpty(chiettinh.get_CongSuatTram()) ? chiettinh.get_CongSuatTram() : "");
				cv.put(COLUMN_MA_GCST, !TextUtils.isEmpty(chiettinh.get_MaGCST()) ? chiettinh.get_MaGCST() : "");
				cv.put(COLUMN_MA_GCSP, !TextUtils.isEmpty(chiettinh.get_MaGCSP()) ? chiettinh.get_MaGCSP() : "");
				cv.put(COLUMN_NGAY_CTINH, !TextUtils.isEmpty(chiettinh.get_NgayChietTinh()) ? chiettinh.get_NgayChietTinh() : "");
				cv.put(COLUMN_DVI_DTU, !TextUtils.isEmpty(chiettinh.get_DonViDauTu()) ? chiettinh.get_DonViDauTu() : "");
				cv.put(COLUMN_NGAY_CHINHSUA, !TextUtils.isEmpty(chiettinh.get_NgayChinhSua()) ? chiettinh.get_NgayChinhSua() : "" );
				cv.put(COLUMN_NGAY_SUBMIT, !TextUtils.isEmpty(chiettinh.get_NgaySubmit()) ? chiettinh.get_NgaySubmit() : "");
				cv.put(COLUMN_TRANG_THAI_HSO, !TextUtils.isEmpty(chiettinh.get_TrangThaiHoSo()) ? chiettinh.get_TrangThaiHoSo() : "");
				cv.put(COLUMN_DIA_CHI, !TextUtils.isEmpty(chiettinh.get_DiaChi()) ? chiettinh.get_DiaChi() : "");
				cv.put(COLUMN_NGAY_ND, !TextUtils.isEmpty(chiettinh.get_NgayND().toString()) ? chiettinh.get_NgayND().toString() : "");

				String whereClause = "rowid" + " = ? ";
				String[] whereArgs = new String[] { ""+chiettinh.get_Id()};

				numRowEffect = dbHelper.getDb().update(TABLE_HSO_CHIETTINH, cv, whereClause, whereArgs);
			}
		} catch (Exception e) {
		}
		finally{
		}

		return numRowEffect;
	}

	public ArrayList<ChietTinhModel> loadChietTinh() {
		ArrayList<ChietTinhModel> result = new ArrayList<ChietTinhModel>();
		Cursor c = null;
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				c = dbHelper.getDb().rawQuery("select rowid, * from "
						+ ChietTinhModel.CHIETTINH_TABLE_NAME 
						+ " order by TRANG_THAI_HSO", null);

				if (c != null && c.getCount() > 0) {
					int colId_MaBanChietTinh = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[0]);
					int colId_ChiPhiTT = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[1]);
					int colId_ChiPhiVT = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[2]);
					int colId_ChiPhiVL = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[3]);
					int colId_ChiPhiVC = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[4]);
					int colId_ChiPhiNC = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[5]);
					int colId_ChiPhiMM = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[6]);
					int colId_ChiPhiTTK = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[7]);
					int colId_ChiPhiC = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[8]);
					int colId_ChiPhiTL = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[9]);
					int colId_ChiPhiK = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[10]);
					int colId_TienGTT = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[11]);
					int colId_TienGTGT = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[12]);
					int colId_SoTien = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[13]);
					int colId_LamTron = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[14]);
					int colId_Lan = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[15]);
					int colId_TrangThai = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[16]);
					int colId_ThucHien = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[17]);
					int colId_MaDonViQuanLy = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[18]);
					int colId_MaYeuCauKhieuNai = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[19]);
					int colId_MaLoaiHoSo = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[20]);
					int colId_TenKhachHang = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[21]);
					int colId_DienThoaiDichVu = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[22]);
					int colId_BienBanKhaoSat = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[23]);
					int colId_NgayKhaoSat = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[24]);
					int colId_NhanVienKhaoSat = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[25]);
					int colId_ThongTinNha = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[26]);
					int colId_ThongTinHopDong = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[27]);
					int colId_MaTram = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[28]);
					int colId_TenTram = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[29]);
					int colId_CongSuatTram = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[30]);
					int colId_MaGCST = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[31]);
					int colId_MaGCSP = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[32]);
					int colId_NgayChietTinh = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[33]);
					int colId_DonViDauTu = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[34]);
					int colId_NgayChinhSua = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[35]);
					int colId_NgaySubmit = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[36]);
					int colId_TrangThaiHoSo = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[37]);
					int colId_DiaChi = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[38]);
					int colId_NgayND = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[39]);

					int colId_Id = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[40]);

					c.moveToFirst();

					do {
						try {

							int id  = c.getInt(colId_Id);

							String maBanChietTinh = c.getString(colId_MaBanChietTinh);
							double chiPhiTT = c.getDouble(colId_ChiPhiTT);
							double chiPhiVT = c.getDouble(colId_ChiPhiVT);
							double chiPhiVL = c.getDouble(colId_ChiPhiVL);
							double chiPhiVC = c.getDouble(colId_ChiPhiVC);
							double chiPhiNC = c.getDouble(colId_ChiPhiNC);
							double chiPhiMM = c.getDouble(colId_ChiPhiMM);
							double chiPhiTTK = c.getDouble(colId_ChiPhiTTK);
							double chiPhiC = c.getDouble(colId_ChiPhiC);
							double chiPhiTL = c.getDouble(colId_ChiPhiTL);
							double chiPhiK = c.getDouble(colId_ChiPhiK);
							double tienGTT = c.getDouble(colId_TienGTT);
							double tienGTGT = c.getDouble(colId_TienGTGT);
							double soTien = c.getDouble(colId_SoTien);
							double lamTron = c.getDouble(colId_LamTron);
							int lan = c.getInt(colId_Lan);
							String trangThai 				= c.getString(colId_TrangThai);
							String thucHien 				= c.getString(colId_ThucHien);
							String maDonViQuanLy 			= c.getString(colId_MaDonViQuanLy);
							String maYeuCauKhieuNai 		= c.getString(colId_MaYeuCauKhieuNai);
							String maLoaiHoSo 				= c.getString(colId_MaLoaiHoSo);
							String tenKhachHang 			= c.getString(colId_TenKhachHang);
							String dienThoaiDichVu 			= c.getString(colId_DienThoaiDichVu);
							String bienBanKhaoSat 			= c.getString(colId_BienBanKhaoSat);
							String ngayKhaoSat 				= c.getString(colId_NgayKhaoSat);
							String nhanVienKhaoSat 			= c.getString(colId_NhanVienKhaoSat);
							String thongTinNha 				= c.getString(colId_ThongTinNha);
							String thongTinHopDong 			= c.getString(colId_ThongTinHopDong);
							String maTram 					= c.getString(colId_MaTram);
							String tenTram 					= c.getString(colId_TenTram);
							String congSuatTram 			= c.getString(colId_CongSuatTram);
							String maGCST 					= c.getString(colId_MaGCST);
							String maGCSP 					= c.getString(colId_MaGCSP);
							String ngayChietTinh 			= c.getString(colId_NgayChietTinh);
							String donViDauTu 				= c.getString(colId_DonViDauTu);
							String ngayChinhSua 			= c.getString(colId_NgayChinhSua);
							String ngaySubmit 				= c.getString(colId_NgaySubmit);
							String trangThaiHoSo 			= c.getString(colId_TrangThaiHoSo);
							String diaChi 					= c.getString(colId_DiaChi);
							String ngayND 					= c.getString(colId_NgayND);

							ChietTinhModel ct = new ChietTinhModel();

							ct.set_MaBanChietTinh(maBanChietTinh);
							ct.set_ChiPhiTT(chiPhiTT);
							ct.set_ChiPhiVT(chiPhiVT);
							ct.set_ChiPhiVL(chiPhiVL);
							ct.set_ChiPhiVC(chiPhiVC);
							ct.set_ChiPhiNC(chiPhiNC);
							ct.set_ChiPhiMM(chiPhiMM);
							ct.set_ChiPhiTTK(chiPhiTTK);
							ct.set_ChiPhiC(chiPhiC);
							ct.set_ChiPhiTL(chiPhiTL);
							ct.set_ChiPhiK(chiPhiK);
							ct.set_TienGTT(tienGTT);
							ct.set_TienGTGT(tienGTGT);
							ct.set_SoTien(soTien);					
							ct.set_LamTron(lamTron);
							ct.set_Lan(lan);

							ct.set_TrangThai(!TextUtils.isEmpty(trangThai) ? trangThai : "");
							ct.set_ThucHien(!TextUtils.isEmpty(thucHien) ? thucHien : "");
							ct.set_MaDonViQuanLy( !TextUtils.isEmpty(maDonViQuanLy) ? maDonViQuanLy : "");
							ct.set_MaYeuCauKhieuNai( !TextUtils.isEmpty(maYeuCauKhieuNai) ? maYeuCauKhieuNai : "");
							ct.set_MaLoaiHoSo( !TextUtils.isEmpty(maLoaiHoSo) ? maLoaiHoSo : "");
							ct.set_TenKhachHang( !TextUtils.isEmpty(tenKhachHang) ? tenKhachHang : "");
							ct.set_DienThoaiDichVu( !TextUtils.isEmpty(dienThoaiDichVu) ? dienThoaiDichVu : "");
							ct.set_BienBanKhaoSat( !TextUtils.isEmpty(bienBanKhaoSat) ? bienBanKhaoSat : "");
							ct.set_NgayKhaoSat( !TextUtils.isEmpty(ngayKhaoSat) ? ngayKhaoSat : "");
							ct.set_NhanVienKhaoSat( !TextUtils.isEmpty(nhanVienKhaoSat) ? nhanVienKhaoSat : "");
							ct.set_ThongTinNha( !TextUtils.isEmpty(thongTinNha) ? thongTinNha : "");
							ct.set_ThongTinHopDong( !TextUtils.isEmpty(thongTinHopDong) ? thongTinHopDong : "");
							ct.set_MaTram( !TextUtils.isEmpty(maTram) ? maTram : "");
							ct.set_TenTram( !TextUtils.isEmpty(tenTram) ? tenTram : "");
							ct.set_CongSuatTram( !TextUtils.isEmpty(congSuatTram) ? congSuatTram : "");
							ct.set_MaGCST(!TextUtils.isEmpty(maGCST) ? maGCST : "");
							ct.set_MaGCSP(!TextUtils.isEmpty(maGCSP) ? maGCSP : "");
							ct.set_NgayChietTinh(!TextUtils.isEmpty(ngayChietTinh) ? ngayChietTinh : "");
							ct.set_DonViDauTu(!TextUtils.isEmpty(donViDauTu) ? donViDauTu : "");
							ct.set_NgayChinhSua(!TextUtils.isEmpty(ngayChinhSua) ? ngayChinhSua : "");
							ct.set_NgaySubmit(!TextUtils.isEmpty(ngaySubmit) ? ngaySubmit : "");
							ct.set_TrangThaiHoSo(!TextUtils.isEmpty(trangThaiHoSo) ? trangThaiHoSo : "");
							ct.set_DiaChi( !TextUtils.isEmpty(diaChi) ? diaChi : "");
							ct.set_NgayND( !TextUtils.isEmpty(ngayND) ? ngayND : "");
							ct.set_Id(id);

							result.add(ct);
						} catch (Exception e) {
						}
					} while (c.moveToNext());
				}
			}
		} catch (Exception e) {
		}finally{
			if(c != null)
			{
				c.close();
				c = null;
			}
		}

		return result;
	}

	public ArrayList<ChietTinhModel> loadChietTinhOrderByNgayKS(int iTrangThaiHoSo) {
		ArrayList<ChietTinhModel> result = new ArrayList<ChietTinhModel>();
		Cursor c = null;

		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				c = dbHelper.getDb().rawQuery("select rowid, * from "
						+ ChietTinhModel.CHIETTINH_TABLE_NAME + " where " + ChietTinhModel.FIELD_TRANG_THAI_HSO + " = ? " 
						+ " ORDER BY (CASE WHEN [NGAY_KSAT] IS NULL THEN 1 ELSE 0 END) DESC, [NGAY_KSAT] DESC", new String[]{""+iTrangThaiHoSo});

				if (c != null && c.getCount() > 0) {

					int colId_MaBanChietTinh = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[0]);
					int colId_ChiPhiTT = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[1]);
					int colId_ChiPhiVT = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[2]);
					int colId_ChiPhiVL = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[3]);
					int colId_ChiPhiVC = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[4]);
					int colId_ChiPhiNC = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[5]);
					int colId_ChiPhiMM = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[6]);
					int colId_ChiPhiTTK = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[7]);
					int colId_ChiPhiC = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[8]);
					int colId_ChiPhiTL = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[9]);
					int colId_ChiPhiK = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[10]);
					int colId_TienGTT = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[11]);
					int colId_TienGTGT = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[12]);
					int colId_SoTien = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[13]);
					int colId_LamTron = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[14]);
					int colId_Lan = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[15]);
					int colId_TrangThai = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[16]);
					int colId_ThucHien = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[17]);
					int colId_MaDonViQuanLy = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[18]);
					int colId_MaYeuCauKhieuNai = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[19]);
					int colId_MaLoaiHoSo = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[20]);
					int colId_TenKhachHang = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[21]);
					int colId_DienThoaiDichVu = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[22]);
					int colId_BienBanKhaoSat = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[23]);
					int colId_NgayKhaoSat = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[24]);
					int colId_NhanVienKhaoSat = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[25]);
					int colId_ThongTinNha = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[26]);
					int colId_ThongTinHopDong = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[27]);
					int colId_MaTram = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[28]);
					int colId_TenTram = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[29]);
					int colId_CongSuatTram = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[30]);
					int colId_MaGCST = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[31]);
					int colId_MaGCSP = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[32]);
					int colId_NgayChietTinh = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[33]);
					int colId_DonViDauTu = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[34]);
					int colId_NgayChinhSua = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[35]);
					int colId_NgaySubmit = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[36]);
					int colId_TrangThaiHoSo = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[37]);
					int colId_DiaChi = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[38]);
					int colId_NgayND = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[39]);

					int colId_Id = c
							.getColumnIndex(ChietTinhModel.CHIETTINH_FULL_PROJECTION[40]);

					c.moveToFirst();

					do {
						try {

							int id  = c.getInt(colId_Id);

							String maBanChietTinh = c.getString(colId_MaBanChietTinh);
							double chiPhiTT = c.getDouble(colId_ChiPhiTT);
							double chiPhiVT = c.getDouble(colId_ChiPhiVT);
							double chiPhiVL = c.getDouble(colId_ChiPhiVL);
							double chiPhiVC = c.getDouble(colId_ChiPhiVC);
							double chiPhiNC = c.getDouble(colId_ChiPhiNC);
							double chiPhiMM = c.getDouble(colId_ChiPhiMM);
							double chiPhiTTK = c.getDouble(colId_ChiPhiTTK);
							double chiPhiC = c.getDouble(colId_ChiPhiC);
							double chiPhiTL = c.getDouble(colId_ChiPhiTL);
							double chiPhiK = c.getDouble(colId_ChiPhiK);
							double tienGTT = c.getDouble(colId_TienGTT);
							double tienGTGT = c.getDouble(colId_TienGTGT);
							double soTien = c.getDouble(colId_SoTien);
							double lamTron = c.getDouble(colId_LamTron);
							int lan = c.getInt(colId_Lan);
							String trangThai 				= c.getString(colId_TrangThai);
							String thucHien 				= c.getString(colId_ThucHien);
							String maDonViQuanLy 			= c.getString(colId_MaDonViQuanLy);
							String maYeuCauKhieuNai 		= c.getString(colId_MaYeuCauKhieuNai);
							String maLoaiHoSo 				= c.getString(colId_MaLoaiHoSo);
							String tenKhachHang 			= c.getString(colId_TenKhachHang);
							String dienThoaiDichVu 			= c.getString(colId_DienThoaiDichVu);
							String bienBanKhaoSat 			= c.getString(colId_BienBanKhaoSat);
							String ngayKhaoSat 				= c.getString(colId_NgayKhaoSat);
							String nhanVienKhaoSat 			= c.getString(colId_NhanVienKhaoSat);
							String thongTinNha 				= c.getString(colId_ThongTinNha);
							String thongTinHopDong 			= c.getString(colId_ThongTinHopDong);
							String maTram 					= c.getString(colId_MaTram);
							String tenTram 					= c.getString(colId_TenTram);
							String congSuatTram 			= c.getString(colId_CongSuatTram);
							String maGCST 					= c.getString(colId_MaGCST);
							String maGCSP 					= c.getString(colId_MaGCSP);
							String ngayChietTinh 			= c.getString(colId_NgayChietTinh);
							String donViDauTu 				= c.getString(colId_DonViDauTu);
							String ngayChinhSua 			= c.getString(colId_NgayChinhSua);
							String ngaySubmit 				= c.getString(colId_NgaySubmit);
							String trangThaiHoSo 			= c.getString(colId_TrangThaiHoSo);
							String diaChi 					= c.getString(colId_DiaChi);
							String ngayND 					= c.getString(colId_NgayND);

							ChietTinhModel ct = new ChietTinhModel();

							ct.set_MaBanChietTinh(maBanChietTinh);
							ct.set_ChiPhiTT(chiPhiTT);
							ct.set_ChiPhiVT(chiPhiVT);
							ct.set_ChiPhiVL(chiPhiVL);
							ct.set_ChiPhiVC(chiPhiVC);
							ct.set_ChiPhiNC(chiPhiNC);
							ct.set_ChiPhiMM(chiPhiMM);
							ct.set_ChiPhiTTK(chiPhiTTK);
							ct.set_ChiPhiC(chiPhiC);
							ct.set_ChiPhiTL(chiPhiTL);
							ct.set_ChiPhiK(chiPhiK);
							ct.set_TienGTT(tienGTT);
							ct.set_TienGTGT(tienGTGT);
							ct.set_SoTien(soTien);					
							ct.set_LamTron(lamTron);
							ct.set_Lan(lan);

							ct.set_TrangThai(!TextUtils.isEmpty(trangThai) ? trangThai : "");
							ct.set_ThucHien(!TextUtils.isEmpty(thucHien) ? thucHien : "");
							ct.set_MaDonViQuanLy( !TextUtils.isEmpty(maDonViQuanLy) ? maDonViQuanLy : "");
							ct.set_MaYeuCauKhieuNai( !TextUtils.isEmpty(maYeuCauKhieuNai) ? maYeuCauKhieuNai : "");
							ct.set_MaLoaiHoSo( !TextUtils.isEmpty(maLoaiHoSo) ? maLoaiHoSo : "");
							ct.set_TenKhachHang( !TextUtils.isEmpty(tenKhachHang) ? tenKhachHang : "");
							ct.set_DienThoaiDichVu( !TextUtils.isEmpty(dienThoaiDichVu) ? dienThoaiDichVu : "");
							ct.set_BienBanKhaoSat( !TextUtils.isEmpty(bienBanKhaoSat) ? bienBanKhaoSat : "");
							ct.set_NgayKhaoSat( !TextUtils.isEmpty(ngayKhaoSat) ? ngayKhaoSat : "");
							ct.set_NhanVienKhaoSat( !TextUtils.isEmpty(nhanVienKhaoSat) ? nhanVienKhaoSat : "");
							ct.set_ThongTinNha( !TextUtils.isEmpty(thongTinNha) ? thongTinNha : "");
							ct.set_ThongTinHopDong( !TextUtils.isEmpty(thongTinHopDong) ? thongTinHopDong : "");
							ct.set_MaTram( !TextUtils.isEmpty(maTram) ? maTram : "");
							ct.set_TenTram( !TextUtils.isEmpty(tenTram) ? tenTram : "");
							ct.set_CongSuatTram( !TextUtils.isEmpty(congSuatTram) ? congSuatTram : "");
							ct.set_MaGCST(!TextUtils.isEmpty(maGCST) ? maGCST : "");
							ct.set_MaGCSP(!TextUtils.isEmpty(maGCSP) ? maGCSP : "");
							ct.set_NgayChietTinh(!TextUtils.isEmpty(ngayChietTinh) ? ngayChietTinh : "");
							ct.set_DonViDauTu(!TextUtils.isEmpty(donViDauTu) ? donViDauTu : "");
							ct.set_NgayChinhSua(!TextUtils.isEmpty(ngayChinhSua) ? ngayChinhSua : "");
							ct.set_NgaySubmit(!TextUtils.isEmpty(ngaySubmit) ? ngaySubmit : "");
							ct.set_TrangThaiHoSo(!TextUtils.isEmpty(trangThaiHoSo) ? trangThaiHoSo : "");
							ct.set_DiaChi( !TextUtils.isEmpty(diaChi) ? diaChi : "");
							ct.set_NgayND( !TextUtils.isEmpty(ngayND) ? ngayND : "");
							ct.set_Id(id);

							result.add(ct);
						} catch (Exception e) {
						}
					} while (c.moveToNext());
				}
			}
		} catch (Exception e) {
		}finally{
			if(c!=null)
			{
				c.close();
				c = null;
			}
		}

		return result;
	}

	public int deleteItemByHoSoChietTinhId(String hosoChietTinhId) {
		int numRowEffect = 0;
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				String whereClause = "rowid " + " = ? ";
				String[] whereArgs = new String[] {hosoChietTinhId};

				numRowEffect = dbHelper.getDb().delete(ChietTinhModel.CHIETTINH_TABLE_NAME, whereClause, whereArgs);
			}
		} catch (Exception e) {
		}
		finally{
		}
		return numRowEffect;
	}

}
